cd ~/share_realestate/hmda

clear
forvalues i=2007/2017{
	di `i'
	append using ./`i'/loans_`i'
}
tab agency_code year,m

merge m:1 year respondent_id agency_code using panel_07-17
keep if _m==3
drop _m

merge m:1 year respondent_id agency_code using avery_panel,keepusing(TYPE ENTITY)
tab year _m
keep if _m==3
drop _m

gen sb=1 if inlist(TYPE,40,41)
replace sb=0 if sb!=1 & TYPE!=.
rename ENTITY rssdid

destring income,replace force
save loans_07-17.dta

clear
forvalues i=2007/2017{
	di `i'
	append using ./`i'/panel_`i',keep(year respondent_id agency_code respondent_name)
}
save panel_07-17.dta

use loans_07-17
replace state_code=substr("00",1,2-length(state_code))+state_code if real(state_code)!=.
replace county_code=substr("000",1,3-length(county_code))+county_code if real(county_code)!=.
merge m:1 county_code state_code year using conforming_post2007,keepusing (conforming_limit)
drop if _m==2
replace conforming_limit = 417000 if year==2007 | real(state_code)==. | real(county_code)==.
drop _m

replace conforming_limit=conforming_limit/1000
save loans_07to17wcfm.dta


use ~/share_realestate/hmda/loans_07to17wcfm.dta

keep if loan_type==1
keep if lien_status==1

gen bandwidth=loan_amount/conforming_limit
gen jumbo = loan_amount/conforming_limit > 1.005 if !missing(loan_amount)
gen conforming = loan_amount/conforming_limit < 1.005

gen held=inlist(purchaser_type,0,8)
egen tract_year=group(state_code county_code census_tract year)
**drop thrifts and credit unions
gen bank=1 if inrange(TYPE,10,14)
replace bank=0 if inlist(TYPE,40,41)
keep if !missing(bank)

save ~/work/loans07to17conventional.dta
export delimited using ~/work/loans07to17conventional.csv


bys rssdid year census_tract:gen cnt=_n==1
bys rssdid year: egen unique_census_tract=sum(cnt)
bys rssdid year: keep if _n==1
keep year rssdid unique_census_tract
save ~/unique_tract_conventional.dta


use ~/work/loans07to17conventional.dta,clear

gen cnt=1
sort rssdid year
by rssdid year: egen jumbo_originations=total(jumbo)
by rssdid year: egen conforming_originations=total(conforming)
by rssdid year: egen total_originations=total(cnt)

by rssdid year: egen jumbo_volume = total(jumbo*loan_amount)
by rssdid year: egen conforming_volume = total(conforming*loan_amount)
by rssdid year: egen total_volume = total(loan_amount)

by rssdid year: egen held_total = total(held)
by rssdid year: egen jumbo_held = total(jumbo*held)
by rssdid year: egen conforming_held = total(conforming*held)

by rssdid year: egen held_volume = total(held*loan_amount)
by rssdid year: egen jumbo_held_volume = total(jumbo*held*loan_amount)
by rssdid year: egen conforming_held_volume = total(conforming*held*loan_amount)

collapse (mean) bank income loan_amount total_originations jumbo_originations conforming_originations jumbo_volume conforming_volume total_volume held_total jumbo_held conforming_held held_volume jumbo_held_volume conforming_held_volume,by(rssdid year)

merge 1:1 year rssdid using unique_tract_conventional.dta
drop if _m==2
drop _m

merge 1:1 year rssdid using balance_sheet
keep if _m==3
drop _m

keep if bank==1
save bankdata_conventional
export delimited bankdata_conventional.csv,replace


use ~/work/loans07to17conventional.dta
bys rssdid year state county census_tract:gen cnt=_n==1
bys rssdid year: egen unique_cts=sum(cnt)
bys rssdid year: keep if _n==1
keep year rssdid unique_cts 
save ~/balance_sheet_paper/unique_cts_conventional.dta

use ~/balance_sheet_paper/bankdata_conventional
merge 1:1 rssdid year using ~/balance_sheet_paper/unique_cts_conventional.dta
keep if _m==3
drop _m
save,replace

**compute cr residual as the cr gap
use ~/balance_sheet_paper/bankdata_conventional
gen log_assets= log(total_assets)
gen log_income = log(income)
gen log_loan_amount = log(loan_amount)
gen deposit_ratio = total_deposits/total_assets
areg cr log_assets log_income log_loan_amount deposit_ratio noncore_funding core_deposits [w=total_volume],absorb(year)
predict cr_gap,r

drop log_assets log_income log_loan_amount
save,replace
export delimited ~/balance_sheet_paper/bankdata_conventional.csv,replace


forvalues i=2007/2017{
	di `i'
	append using ./`i'/all_records_`i'
}
replace state_code=substr("00",1,2-length(state_code))+state_code if real(state_code)!=.
replace county_code=substr("000",1,3-length(county_code))+county_code if real(county_code)!=.
merge m:1 county_code state_code year using conforming_post2007,keepusing (conforming_limit)
drop if _m==2
replace conforming_limit = 417000 if year==2007 | real(state_code)==. | real(county_code)==.
drop _m
export delimited using all_records07-17wcfm.csv